package com.zhouyu.exampleboot.util;

import com.zhouyu.exampleboot.entity.UserPoi;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.List;

/**
 * poi工具类
 */
public class PoiUtils {

    static Logger logger = LoggerFactory.getLogger(PoiUtils.class);

    /**
     * @param fieldNames
     * @param list
     * @return
     */
    public static SXSSFWorkbook writeExcel(List<String> fieldNames, List<UserPoi> list) {
        // 内存中保留 2000 条数据，以免内存溢出，其余写入 硬盘
        SXSSFWorkbook wb = new SXSSFWorkbook(2000);
        // 创建一个Excel的sheet
        Sheet sheet = wb.createSheet("sheet1");

        createHead(sheet, fieldNames, wb);

        //创建表单数据
        for (int i = 0; i < list.size(); i++) {

            //创建行
            Row dataRow = sheet.createRow(i + 2);
            UserPoi user = list.get(i);

            //单元格游标
            int cellCursor = 0;
            Cell cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getId() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getName() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getSex() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getAge() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getClassId() + "");

        }
        /*
         * for (int k = 0; k < fieldNames.size(); k++ ) {
         * // poi提供的自动调整每列的宽度,但是不兼容中文调整
         * sheet.autoSizeColumn(k); }
         */
        // setSizeColumn(sheet, fieldNames.size());
        return wb;
    }

    /**
     * 导出图片到excel中
     */
    public static SXSSFWorkbook writeExcelWithphoto(List<String> fieldNames, List<UserPoi> list) {
        //创建工作簿
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet imgsheet = workbook.createSheet("imgsheet");
        Drawing patriarch = imgsheet.createDrawingPatriarch();
        createHead(imgsheet, fieldNames, workbook);
        imgsheet.setColumnWidth(0, 256*25+100);//列宽

        //创建表单数据
        for (int i = 0; i < list.size(); i++) {
            Row dataRow = imgsheet.createRow(i + 2); //创建行
            dataRow.setHeight((short) (256 * 15));//设置行高
            UserPoi user = list.get(i);

            //单元格游标
            int cellCursor = 0;
            Cell cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getId() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getName() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getSex() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getAge() + "");
            cell = dataRow.createCell(cellCursor++);
            cell.setCellValue(user.getClassId() + "");
            //插入图片
            cell = dataRow.createCell(cellCursor++);
            String imgUrl = "https://survey.s3.cn-north-1.amazonaws.com.cn/shenzhen/topicBank/4c6f92dc-cd76-4bed-91e9-25d4daf5abc9/1546051381672.png";
            if (!StringUtils.isEmpty(imgUrl)) {
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cellCursor, i+2, (short) cellCursor + 1, i+2 + 1);

                byte[] bytes = PoiUtils.readInputStremOfImg(imgUrl);
                anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
                patriarch.createPicture(anchor, workbook.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG));
            }
        }
        return workbook;

    }

    public static void createHead(Sheet sheet, List<String> fieldNames, SXSSFWorkbook wb) {
        int cursor = 0;
        //创建标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fieldNames.size()));//表头跨列合并（起始行号，终止行号，起始列，终止列）
        Row row = sheet.createRow(cursor++);
        String title = "2018年调查报表统计报表";
        Cell cell1Title = row.createCell(0);
        cell1Title.setCellValue(title);

        //创建表头
        Row rowHead = sheet.createRow(cursor++);
        for (int i = 0; i < fieldNames.size(); i++) {
            Cell cell = rowHead.createCell(i);
            cell.setCellValue(fieldNames.get(i));
            cell.setCellStyle(getColumnTopStyle(wb));//样式
        }
    }
    public static void createHead2(Sheet sheet, List<String> fieldNames, XSSFWorkbook wb) {
        int cursor = 0;
        //创建标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fieldNames.size()));//表头跨列合并（起始行号，终止行号，起始列，终止列）
        Row row = sheet.createRow(cursor++);
        String title = "2018年调查报表统计报表";
        Cell cell1Title = row.createCell(0);
        cell1Title.setCellValue(title);

        //创建表头
        Row rowHead = sheet.createRow(cursor++);
        for (int i = 0; i < fieldNames.size(); i++) {
            Cell cell = rowHead.createCell(i);
            cell.setCellValue(fieldNames.get(i));
          //  cell.setCellStyle(getColumnTopStyle(wb));//样式
        }
    }

    /**
     * 导出图片
     *
     * @param imgUrl    图片地址
     * @param workbook
     * @param patriarch 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
     * @param rowIndex  行号
     */
    public static void insertImg(String imgUrl, SXSSFWorkbook workbook, Drawing patriarch, int rowIndex,int cellCursor) {
        try {
            byte[] data = readInputStremOfImg(imgUrl);
            /*
             * 该构造函数有8个参数
             * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
             * 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后两个参数对应的表示图片右下角所在的cellNum和 rowNum，
             * excel中的cellNum和rowNum的index都是从0开始的
             */
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cellCursor, rowIndex, (short) cellCursor + 1, rowIndex + 1);
            anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
            patriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (Exception e) {
            logger.info("导出图片异常!!!");
        }
    }

    public static byte[] readInputStremOfImg(String imgUrl){
        HttpURLConnection connection=null;
        InputStream inStream = null;
        ByteArrayOutputStream outStream = null;
        try {
            URL url = new URL(imgUrl);
            //打开链接
            connection = (HttpURLConnection) url.openConnection();
            //设置请求方式为"GET"
            connection.setRequestMethod("GET");
            //超时响应时间为50秒
            connection.setConnectTimeout(50 * 1000);
            //通过输入流获取图片数据
            inStream = connection.getInputStream();
            outStream = new ByteArrayOutputStream();
            //创建一个Buffer字符串
            byte[] buffer = new byte[1024];
            //每次读取的字符串长度，如果为-1，代表全部读取完毕
            int len = 0;
            //使用一个输入流从buffer里把数据读取出来
            while ((len = inStream.read(buffer)) != -1) {
                //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
                outStream.write(buffer, 0, len);
            }
        } catch (IOException e) {
            logger.error("图片流读入异常:{}",e.getMessage());
        } finally {
            //关闭输入流
            try {
                inStream.close();
            } catch (IOException e) {
                logger.error("图片流关闭异常:{}",e.getMessage());
            }
        }
        //把outStream里的数据写入内存
        return outStream.toByteArray();

    }


    /**
     * Description: 1、调整列宽,兼容中文
     *
     * @param sheet
     * @param size
     * @author: yizl
     * @date: Nov 1, 2018 11:31:56 AM
     */
    private static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                // 当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (((Row) currentRow).getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }

            // Excel的长度为字节码长度*256,*1.3为了处理数字格式
            columnWidth = (int) Math.floor(columnWidth * 256 * 1.3);
            //单元格长度大于20000的话也不美观,设置个最大长度
            columnWidth = columnWidth >= 20000 ? 20000 : columnWidth;
            //设置每列长度
            sheet.setColumnWidth(columnNum, columnWidth);
        }
    }

    /**
     * 返回给浏览器
     *
     * @param request
     * @param response
     * @param fileName
     */
    public static void writeToResponse(HttpServletRequest request, HttpServletResponse response, String fileName, List<String> feilds, List<UserPoi> userList) {
        SXSSFWorkbook sxssfWorkbook = writeExcel(feilds, userList);
        OutputStream os = null;
        try {
            //获得浏览器种类
            String userAgent = request.getHeader("User-Agent");
            // 解决中文乱码问题
            String fileName1 = "Excel-" + fileName + ".xlsx";
            String newFilename = URLEncoder.encode(fileName1, "UTF8");
            // 如果没有userAgent，则默认使用IE的方式进行编码，因为毕竟IE还是占多数的
            String rtn = "filename=\"" + newFilename + "\"";
            if (userAgent != null) {
                userAgent = userAgent.toLowerCase();
                // IE浏览器，只能采用URLEncoder编码
                if (userAgent.indexOf(1) != -1) {
                    rtn = "filename=\"" + newFilename + "\"";
                }
                // Opera浏览器只能采用filename*
                else if (userAgent.indexOf(2) != -1) {
                    rtn = "filename*=UTF-8''" + newFilename;
                }
                // Safari浏览器，只能采用ISO编码的中文输出
                else if (userAgent.indexOf(3) != -1) {
                    rtn = "filename=\"" + new String(fileName1.getBytes("UTF-8"), "ISO8859-1")
                            + "\"";
                }
                // FireFox浏览器，可以使用MimeUtility或filename*或ISO编码的中文输出
                else if (userAgent.indexOf(4) != -1) {
                    rtn = "filename*=UTF-8''" + newFilename;
                }
            }

            String headStr = "attachment;  " + rtn;
            response.setContentType("APPLICATION/ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", headStr);
            // 响应到客户端
            os = response.getOutputStream();
            sxssfWorkbook.write(os);
        } catch (UnsupportedEncodingException e) {
            logger.error("报表导出异常！！！");
        } catch (IOException e) {
            logger.error("报表导出异常！！！");
        } finally {
            try {
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                logger.error("流关闭异常！！！");
            }
        }
    }

    /**
     * 导出到指定路径
     *
     * @param fileName
     */
    public static void writeToLocal(String fileName, List<String> feilds, List<UserPoi> userList) {
        SXSSFWorkbook sxssfWorkbook = writeExcel(feilds, userList);
        FileOutputStream fileOutputSteam = null;
        try {
            // new File("E:\\learn\\sheet\\11.xls");
            fileOutputSteam = new FileOutputStream(new File(fileName));
            sxssfWorkbook.write(fileOutputSteam);
        } catch (IOException e) {
            logger.error("报表导出异常！！！");
        } finally {
            try {
                if (fileOutputSteam != null) {
                    fileOutputSteam.close();
                }
            } catch (IOException e) {
                logger.error("流关闭异常！！！");
            }
        }
    }


    /**
     * Description: 1、列头单元格样式
     *
     * @param workbook
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:22:44 PM
     */
    public static CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {

        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * Description: 1、设置表体的单元格样式
     *
     * @param workbook
     * @return
     * @author: yizl
     * @date: Oct 30, 2018 1:18:42 PM
     */
    private static CellStyle getBodyStyle(SXSSFWorkbook workbook) {
        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 设置单元格居中对齐
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 创建单元格内容不显示自动换行
        cellStyle.setWrapText(false);
        // 设置单元格字体样式
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setFontName("Courier New");// 设置字体
        font.setFontHeight(11);// 设置字体的大小
        cellStyle.setFont(font);// 将字体添加到表格中去
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        return cellStyle;
    }
}
